﻿using System;
using System.Collections.Generic;
using System.Text;
using RockCompany.Common.Lib;
using RockCompany.Master.DTO;
using RockCompany.Common.Constant;
using System.Windows.Forms;
using System.Data;

namespace RockCompany.Master.Model
{
    public class SI06FormModel : CommonModel
    {
        #region Properties
        private DataTable _newProdExportDetail;
        public DataTable newProdExportDetail
        {
            get { return _newProdExportDetail; }
            set { _newProdExportDetail = value; }
        }
        #endregion

        #region Constructor
        public SI06FormModel()
        {
            newProdExportDetail = new DataTable();
            DataList = new List<object>();
            dbConnector = new DBConnector();
        }
        #endregion

        #region Utility Methods
        public DataRow getCheckedRow(int index)
        {
            DataRow curRow = newProdExportDetail.Rows[index];
            curRow.SetField<bool>(PHIEU_XK_TP_CT_DTO.N_CHON, !curRow.Field<bool>(PHIEU_XK_TP_CT_DTO.N_CHON));
            return curRow;
        }

        private DataTable searchWorkOrderDetail(List<string> sWorkOrderList)
        {
            DataTable tempDB = null;
            if (sWorkOrderList.Count > 0)
            {
                string sSql = "SELECT " + CONG_LENH_SX.N_SO_LO + "," + CONG_LENH_SX.N_MA_MAU;
                sSql += " FROM " + CONG_LENH_SX.N_TABLE;
                sSql += " WHERE " + CONG_LENH_SX.N_SO_LO + " IN (";
                foreach (string s in sWorkOrderList)
                {
                    sSql += "'" + s + "',";
                }
                sSql = sSql.Substring(0, sSql.Length - 1) + ")";
                tempDB = selectData(sSql);
            }
            return tempDB;
        }
        #endregion

        #region Main Methods
        public DataTable searchProdExportList(string sNo, string sfromDate, string sToDate)
        {
            string sSql = "SELECT *";
            sSql += " FROM " + PHIEU_XK_TP_DTO.N_TABLE + "";
            sSql += " WHERE " + PHIEU_XK_TP_DTO.N_MS_PHIEU_XK_TP + " LIKE '" + sNo + "%'";
            if (!sfromDate.Equals(CommonConstantIF.BLANK))
            {
                sSql += " AND CONVERT(VARCHAR," + PHIEU_XK_TP_DTO.N_NGAY_XK + ",112) BETWEEN " + sfromDate + " AND " + sToDate;
            }

            return selectData(sSql);
        }

        public DataTable searchProdExportDetail(string sProdExportNo)
        {
            string sSql = "SELECT CONVERT(BIT,'0') AS CHON, XK.*, CL.MA_MAU";
            sSql += " FROM " + PHIEU_XK_TP_CT_DTO.N_TABLE + " XK, " + CONG_LENH_SX.N_TABLE + " CL";
            sSql += " WHERE XK.SO_LO = CL.SO_LO AND " + PHIEU_XK_TP_CT_DTO.N_MS_PHIEU_XK_TP + " = '" + sProdExportNo + "'";

            //DataTable res = selectData(sSql);
            //Util.SetRowStatusMod(res);
            return selectData(sSql);
        }

        public DataTable searchProdExportDetail2(string sProdExportNo)
        {
            string sSql = "SELECT CONVERT(BIT,'0') AS CHON, *";
            sSql += " FROM " + PHIEU_XK_TP_CT_DTO.N_TABLE;
            sSql += " WHERE " + PHIEU_XK_TP_CT_DTO.N_MS_PHIEU_XK_TP + " = '" + sProdExportNo + "'";

            return selectData(sSql);
        }

        public bool addNewProdExportDetail(string sUserName, List<string> sWorkOrderList)
        {
            DataTable tempDB = searchWorkOrderDetail(sWorkOrderList);
            if (tempDB != null && tempDB.Rows.Count > 0)
            {
                foreach (DataRow curRow in tempDB.Rows)
                {
                    DataRow newRow = newProdExportDetail.NewRow();
                    newRow.SetField<bool>(PHIEU_XK_TP_CT_DTO.N_CHON, false);
                    newRow.SetField<string>(PHIEU_XK_TP_CT_DTO.N_SO_LO, curRow.Field<string>(CONG_LENH_SX.N_SO_LO));
                    newRow.SetField<string>(PHIEU_XK_TP_CT_DTO.N_MA_MAU, curRow.Field<string>(CONG_LENH_SX.N_MA_MAU));
                    newRow.SetField<decimal>(PHIEU_XK_TP_CT_DTO.N_SL_THUC_XUAT, 0);
                    newRow.SetField<string>(PHIEU_XK_TP_CT_DTO.N_SO_LO, curRow.Field<string>(CONG_LENH_SX.N_SO_LO));
                    newRow.SetField<string>(PHIEU_XK_TP_CT_DTO.N_MA_MAU, curRow.Field<string>(CONG_LENH_SX.N_MA_MAU));
                    newRow.SetField<string>(PHIEU_XK_TP_CT_DTO.N_GHI_CHU, "");
                    newProdExportDetail.Rows.Add(newRow);
                }
            }
            return tempDB != null && tempDB.Rows.Count > 0;
        }

        public bool removeProdExportDetail(string sProdExportNo)
        {
            string sWhereClause = " WHERE ";
            sWhereClause += PHIEU_XK_TP_CT_DTO.N_MS_PHIEU_XK_TP + " ='" + sProdExportNo + "' AND ";
            sWhereClause += PHIEU_XK_TP_CT_DTO.N_SO_LO + " IN(";
            Dictionary<string, object> pv = new Dictionary<string, object>();
            foreach (DataRow curRow in DataList)
            {
                if (curRow.RowState != DataRowState.Added)
                {
                    string sP = "@" + PHIEU_XK_TP_CT_DTO.N_SO_LO + DataList.IndexOf(curRow).ToString();
                    sWhereClause += sP + ",";
                    pv.Add(sP, curRow.Field<string>(PHIEU_XK_TP_CT_DTO.N_SO_LO));
                }
                newProdExportDetail.Rows.Remove(curRow);
            }
            DataList.Clear();
            if (pv.Count > 0)
            {
                sWhereClause = sWhereClause.Substring(0, sWhereClause.Length - 1) + ")";
                if (removeRow(PHIEU_XK_TP_CT_DTO.N_TABLE, sWhereClause, pv))
                    pv.Clear();
            }
            return pv.Count == 0;
        }

        public bool insertPhieuXK(PHIEU_XK_TP_DTO curProdExport, string sCreator)
        {
            if (insertNewProdExport(curProdExport))
            {
                foreach (DataRow row in newProdExportDetail.Rows)
                {
                    if (!insertNewProdExportDetail(sCreator, curProdExport.MS_PHIEU_XK_TP, row)) { return false; }
                }
                return true;
            }
            return false;
        }

        public bool insertNewProdExport(PHIEU_XK_TP_DTO curProdExport)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            pv.Add(PHIEU_XK_TP_DTO.N_MS_PHIEU_XK_TP, curProdExport.MS_PHIEU_XK_TP);
            pv.Add(PHIEU_XK_TP_DTO.N_NGAY_XK, curProdExport.NGAY_XK);
            pv.Add(PHIEU_XK_TP_DTO.N_PHE_DUYET, curProdExport.PHE_DUYET);

            pv.Add(PHIEU_XK_TP_DTO.N_CREATION_TIME, DateTime.Now);
            pv.Add(PHIEU_XK_TP_DTO.N_CREATION_PROGRAM_ID, "SI06A");
            pv.Add(PHIEU_XK_TP_DTO.N_CREATION_USER, curProdExport.CREATION_USER);
            return insertData(PHIEU_XK_TP_DTO.N_TABLE, pv);
        }


        private bool insertNewProdExportDetail(string sCreator, string sProdExportNo, DataRow newRow)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            pv.Add(PHIEU_XK_TP_CT_DTO.N_MS_PHIEU_XK_TP, sProdExportNo);
            pv.Add(PHIEU_XK_TP_DTO.N_MS_PHIEU_XH, newRow.Field<string>(PHIEU_XK_TP_DTO.N_MS_PHIEU_XH));
            pv.Add(PHIEU_XK_TP_CT_DTO.N_SO_LO, newRow.Field<string>(PHIEU_XK_TP_CT_DTO.N_SO_LO));
            pv.Add(PHIEU_XK_TP_CT_DTO.N_MA_MAU, newRow.Field<string>(PHIEU_XK_TP_CT_DTO.N_MA_MAU));
            
            pv.Add(PHIEU_XK_TP_CT_DTO.N_SL_THUC_XUAT, newRow.Field<decimal>(PHIEU_XK_TP_CT_DTO.N_SL_THUC_XUAT));
            pv.Add(PHIEU_XK_TP_CT_DTO.N_DON_VI, Util.parseString(newRow[PHIEU_XK_TP_CT_DTO.N_DON_VI]));
            pv.Add(PHIEU_XK_TP_CT_DTO.N_QL_KHO, Util.parseString(newRow[PHIEU_XK_TP_CT_DTO.N_QL_KHO]));
            pv.Add(PHIEU_XK_TP_CT_DTO.N_DON_VI_LANH, Util.parseString(newRow[PHIEU_XK_TP_CT_DTO.N_DON_VI_LANH]));
            pv.Add(PHIEU_XK_TP_CT_DTO.N_GHI_CHU, Util.parseString(newRow[PHIEU_XK_TP_CT_DTO.N_GHI_CHU]));

            pv.Add(PHIEU_XK_TP_CT_DTO.N_CREATION_PROGRAM_ID, "SI06A");
            pv.Add(PHIEU_XK_TP_CT_DTO.N_CREATION_TIME, DateTime.Now);
            pv.Add(PHIEU_XK_TP_CT_DTO.N_CREATION_USER, sCreator);
            return insertData(PHIEU_XK_TP_CT_DTO.N_TABLE, pv);
        }

        public bool updateProdExport(PHIEU_XK_TP_DTO curProdExport)
        {
            string sClause = "";
            sClause += PHIEU_XK_TP_DTO.N_MS_PHIEU_XK_TP + " = @" + PHIEU_XK_TP_DTO.N_MS_PHIEU_XK_TP;

            Dictionary<string, object> updateValues = new Dictionary<string, object>();

            updateValues.Add(PHIEU_XK_TP_DTO.N_MS_PHIEU_XK_TP, curProdExport.MS_PHIEU_XK_TP);
            updateValues.Add(PHIEU_XK_TP_DTO.N_PHE_DUYET, curProdExport.PHE_DUYET);

            updateValues.Add(PHIEU_XK_TP_DTO.N_LAST_UPDATE_TIME, DateTime.Now);
            updateValues.Add(PHIEU_XK_TP_DTO.N_LAST_UPDATE_PROGRAM_ID, "SI06A");
            updateValues.Add(PHIEU_XK_TP_DTO.N_LAST_UPDATE_USER, curProdExport.LAST_UPDATE_USER);
            return updateData2(PHIEU_XK_TP_DTO.N_TABLE, sClause, updateValues);
        }


        private bool updateNewProdExportDetail(string sUpdater, string sProdExportNo, DataRow modRow)
        {
            string sClause = CommonConstantIF.BLANK;
            sClause += PHIEU_XK_TP_CT_DTO.N_MS_PHIEU_XK_TP + " = @" + PHIEU_XK_TP_CT_DTO.N_MS_PHIEU_XK_TP;
            sClause += " AND " + PHIEU_XK_TP_CT_DTO.N_SO_LO + " = @" + PHIEU_XK_TP_CT_DTO.N_SO_LO;

            Dictionary<string, object> updateValues = new Dictionary<string, object>();
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_MS_PHIEU_XK_TP, sProdExportNo);
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_SO_LO, modRow.Field<string>(PHIEU_XK_TP_CT_DTO.N_SO_LO));
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_SL_THUC_XUAT, modRow.Field<decimal>(PHIEU_XK_TP_CT_DTO.N_SL_THUC_XUAT));
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_DON_VI, modRow.Field<string>(PHIEU_XK_TP_CT_DTO.N_DON_VI));
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_DON_VI_LANH, modRow.Field<string>(PHIEU_XK_TP_CT_DTO.N_DON_VI_LANH));
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_QL_KHO, modRow.Field<string>(PHIEU_XK_TP_CT_DTO.N_QL_KHO));
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_LAST_UPDATE_TIME, DateTime.Now);
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_LAST_UPDATE_PROGRAM_ID, "SI06A");
            updateValues.Add(PHIEU_XK_TP_CT_DTO.N_LAST_UPDATE_USER, sUpdater);
            return updateData2(PHIEU_XK_TP_CT_DTO.N_TABLE, sClause, updateValues);
        }

        public bool saveData(string sProdExportNo, string sUserName)
        {
            DataTable tempDb = null;
            int insertRes = 0, updateRes = 0;

            tempDb = newProdExportDetail.GetChanges(DataRowState.Modified);
            int updatedRowCount = (tempDb != null ? tempDb.Rows.Count : 0);

            tempDb = newProdExportDetail.GetChanges(DataRowState.Added);
            int addedRowCount = (tempDb != null ? tempDb.Rows.Count : 0);

            tempDb = newProdExportDetail.GetChanges();
            if (tempDb != null)
            {
                foreach (DataRow curRow in tempDb.Rows)
                {
                    switch (curRow.RowState)
                    {
                        case DataRowState.Added:
                            if (insertNewProdExportDetail(sUserName, sProdExportNo, curRow))
                                insertRes++;
                            break;
                        case DataRowState.Modified:
                            if (updateNewProdExportDetail(sUserName, sProdExportNo, curRow))
                                updateRes++;
                            break;
                    }
                }
                newProdExportDetail.AcceptChanges();
            }
            return insertRes == addedRowCount && updateRes == updatedRowCount;
        }

        public bool removePhieuXK(string msPhieuXK)
        {
            string sWhereClause = " WHERE ";
            sWhereClause += PHIEU_XK_TP_DTO.N_MS_PHIEU_XK_TP + " IN(";

            sWhereClause += "'" + msPhieuXK + "'";
                
            sWhereClause += ")";

            return removeRow(PHIEU_XK_TP_CT_DTO.N_TABLE, sWhereClause, null) && removeRow(PHIEU_XK_TP_DTO.N_TABLE, sWhereClause, null);
        }

        public bool export2Excel(string newPath, string msPhieu)
        {
            bool b = false;
            if (!CommonConstantIF.BLANK.Equals(newPath))
            {
                DataTable phieu = searchProdExportList(msPhieu, CommonConstantIF.BLANK, CommonConstantIF.BLANK);
                DataTable phieuDetails = searchProdExportDetail(msPhieu);
                if (CommonModel.existData(phieu))
                {
                    ExcelExporter excelExport = new ExcelExporter();
                    excelExport.Trans = this.translator;

                    string fileName = "SI06";

                    excelExport.open(fileName);

                    excelExport.inputValue(new string[] { "B4", "B4" }, Util.FormatDate(Util.parseDateTime(phieu.Rows[0][PHIEU_XK_TP_DTO.N_CREATION_TIME])), false);
                    excelExport.inputValue(new string[] { "E4", "E4" }, phieu.Rows[0][PHIEU_XK_TP_DTO.N_MS_PHIEU_XK_TP], false);
                    int startIndex = 6, count = 0, curIndex = 0;
                    foreach (DataRow curRow in phieuDetails.Rows)
                    {
                        curIndex = startIndex + count;
                        
                        excelExport.inputValue(new string[] { "A" + curIndex, "A" + curIndex }, curRow[PHIEU_XK_TP_CT_DTO.N_MA_MAU ], false);
                        excelExport.inputValue(new string[] { "B" + curIndex, "B" + curIndex }, curRow[PHIEU_XK_TP_CT_DTO.N_SO_LO], false);
                        excelExport.inputValue(new string[] { "C" + curIndex, "C" + curIndex }, curRow[PHIEU_XK_TP_CT_DTO.N_DON_VI], false);
                        excelExport.inputValue(new string[] { "D" + curIndex, "D" + curIndex }, curRow[PHIEU_XK_TP_CT_DTO.N_SL_THUC_XUAT], false);
                        excelExport.inputValue(new string[] { "E" + curIndex, "E" + curIndex }, curRow[PHIEU_XK_TP_CT_DTO.N_GHI_CHU], false);
                        count++;
                    }

                    b = excelExport.saveExcel(newPath);
                    excelExport.close();
                }
            }
            return b;
        }
        #endregion
    }
}
